from flask import Blueprint, render_template, request, redirect, url_for, flash
from database import get_db

# 创建因子分类专用Blueprint
factor_category_bp = Blueprint('factor_category', __name__, url_prefix='/factors/categories')

@factor_category_bp.route('/')
def category_list():
    db = get_db()
    search_query = request.args.get('search', '').strip()
    
    if search_query:
        categories = db.execute('''
            WITH RECURSIVE category_hierarchy AS (
                SELECT 
                    id, 
                    name, 
                    level, 
                    parent_id,
                    CAST(printf('%03d', id) AS TEXT) AS hierarchy_path
                FROM factor_categories
                WHERE id = 1
                
                UNION ALL
                
                SELECT 
                    c.id, 
                    c.name, 
                    c.level, 
                    c.parent_id,
                    CONCAT(ch.hierarchy_path, '.', printf('%03d', c.id)) AS hierarchy_path
                FROM factor_categories c
                JOIN category_hierarchy ch ON c.parent_id = ch.id
                ORDER BY c.id
            )
            SELECT c.id, c.name, c.level, c.parent_id, COUNT(child.id) as child_count 
            FROM factor_categories c
            LEFT JOIN factor_categories child ON c.id = child.parent_id
            JOIN category_hierarchy ch ON c.id = ch.id
            WHERE c.name LIKE ?
            GROUP BY c.id, ch.hierarchy_path
            ORDER BY ch.hierarchy_path
        ''', ('%' + search_query + '%',)).fetchall()
    else:
        categories = db.execute('''
            WITH RECURSIVE category_hierarchy AS (
                SELECT 
                    id, 
                    name, 
                    level, 
                    parent_id,
                    CAST(printf('%03d', id) AS TEXT) AS hierarchy_path
                FROM factor_categories
                WHERE id = 1
                
                UNION ALL
                
                SELECT 
                    c.id, 
                    c.name, 
                    c.level, 
                    c.parent_id,
                    CONCAT(ch.hierarchy_path, '.', printf('%03d', c.id)) AS hierarchy_path
                FROM factor_categories c
                JOIN category_hierarchy ch ON c.parent_id = ch.id
                ORDER BY c.id
            )
            SELECT c.id, c.name, c.level, c.parent_id, COUNT(child.id) as child_count 
            FROM factor_categories c
            LEFT JOIN factor_categories child ON c.id = child.parent_id
            JOIN category_hierarchy ch ON c.id = ch.id
            GROUP BY c.id, ch.hierarchy_path
            ORDER BY ch.hierarchy_path
        ''').fetchall()
    
    return render_template('factor_categories.html', categories=categories, search_query=search_query)

@factor_category_bp.route('/add', methods=['GET', 'POST'])
def add_category():
    db = get_db()
    if request.method == 'POST':
        name = request.form['name']
        parent_id = request.form.get('parent_id', 0)
        
        parent_level = 0
        if parent_id and parent_id != '0':
            parent = db.execute('SELECT level FROM factor_categories WHERE id = ?', (parent_id,)).fetchone()
            if parent:
                parent_level = parent['level']
        
        new_level = parent_level + 1
        if new_level > 5:
            flash('选择的父分类层级过高，分类层级不能超过5级', 'danger')
            return redirect(url_for('factor_category.add_category'))
        
        if not name:
            flash('分类名称不能为空', 'danger')
            return redirect(url_for('factor_category.add_category'))
        
        level = 1
        if parent_id:
            parent = db.execute('SELECT level FROM factor_categories WHERE id = ?', (parent_id,)).fetchone()
            if not parent:
                flash('父分类不存在', 'danger')
                return redirect(url_for('factor_category.add_category'))
            level = parent['level'] + 1
            if level > 5:
                flash('分类层级不能超过5级', 'danger')
                return redirect(url_for('factor_category.add_category'))
        
        exists = db.execute(
            'SELECT id FROM factor_categories WHERE name = ? AND parent_id = ?',
            (name, parent_id)
        ).fetchone()
        if exists:
            flash('同级分类名称不能重复', 'danger')
            return redirect(url_for('factor_category.add_category'))
        
        db.execute(
            'INSERT INTO factor_categories (name, parent_id, level) VALUES (?, ?, ?)',
            (name, parent_id, level)
        )
        db.commit()
        flash('分类添加成功', 'success')
        return redirect(url_for('factor_category.category_list'))
    
    # 获取父分类列表
    parents = db.execute(
        '''WITH RECURSIVE category_hierarchy AS (
               SELECT id, name, parent_id, 
                      CAST(printf('%03d', id) AS TEXT) AS hierarchy_path, 
                      1 AS level
               FROM factor_categories
               WHERE parent_id = 0 OR parent_id IS NULL
               UNION ALL
               SELECT cc.id, cc.name, cc.parent_id, 
                      ch.hierarchy_path || '.' || printf('%03d', cc.id) AS hierarchy_path, 
                      ch.level + 1 AS level
               FROM factor_categories cc
               JOIN category_hierarchy ch ON cc.parent_id = ch.id
           )
           SELECT id, name, level, hierarchy_path
           FROM category_hierarchy
           ORDER BY hierarchy_path'''
    ).fetchall()
    return render_template('factor_add_category.html', parents=parents)

@factor_category_bp.route('/edit/<int:id>', methods=['GET', 'POST'])
def edit_category(id):
    db = get_db()
    category = db.execute('SELECT * FROM factor_categories WHERE id = ?', (id,)).fetchone()
    
    if not category:
        flash('分类不存在', 'danger')
        return redirect(url_for('factor_category.category_list'))
    
    if request.method == 'POST':
        name = request.form['name']
        new_parent_id = request.form.get('parent_id', 0)
        
        if str(new_parent_id) == str(id):
            flash('不能选择自身作为父分类', 'danger')
            return redirect(url_for('factor_category.edit_category', id=id))
        
        new_parent_level = 0
        if new_parent_id and new_parent_id != '0':
            new_parent = db.execute('SELECT level FROM factor_categories WHERE id = ?', (new_parent_id,)).fetchone()
            if new_parent:
                new_parent_level = new_parent['level']
        
        new_level = new_parent_level + 1
        if new_level > 5:
            flash('选择的父分类层级过高，分类层级不能超过5级', 'danger')
            return redirect(url_for('factor_category.edit_category', id=id))
        
        db.execute(
            'UPDATE factor_categories SET name = ?, parent_id = ?, level = ? WHERE id = ?',
            (name, new_parent_id, new_level, id)
        )
        db.commit()
        flash('分类更新成功', 'success')
        return redirect(url_for('factor_category.category_list'))
    
    # 获取父分类列表（排除当前分类及其子分类）
    parents = db.execute(
        '''WITH RECURSIVE category_hierarchy AS (
               SELECT id, name, parent_id, 
                      CAST(printf('%03d', id) AS TEXT) AS hierarchy_path, 
                      1 AS level
               FROM factor_categories
               WHERE (parent_id = 0 OR parent_id IS NULL) AND id != ?
               UNION ALL
               SELECT cc.id, cc.name, cc.parent_id, 
                      ch.hierarchy_path || '.' || printf('%03d', cc.id) AS hierarchy_path, 
                      ch.level + 1 AS level
               FROM factor_categories cc
               JOIN category_hierarchy ch ON cc.parent_id = ch.id
               WHERE cc.id != ?
           )
           SELECT id, name, level, hierarchy_path
           FROM category_hierarchy
           WHERE level < 5 AND id NOT IN (
               WITH RECURSIVE children AS (
                   SELECT id FROM factor_categories WHERE parent_id = ?
                   UNION ALL
                   SELECT cc.id FROM factor_categories cc JOIN children c ON cc.parent_id = c.id
               ) SELECT id FROM children
           )
           ORDER BY hierarchy_path''',
        (id, id, id)
    ).fetchall()
    return render_template('factor_edit_category.html', category=category, parents=parents)

@factor_category_bp.route('/<int:id>/delete', methods=['POST'])
def delete_category(id):
    db = get_db()
    
    # 检查是否有子分类
    has_children = db.execute(
        'SELECT id FROM factor_categories WHERE parent_id = ?', (id,)
    ).fetchone()
    if has_children:
        flash('无法删除包含子分类的分类', 'danger')
        return redirect(url_for('factor_category.category_list'))
    
    # 暂时注释掉因子关联检查，因为carbon_factors表尚未创建
    # has_factors = db.execute(
    #     'SELECT id FROM carbon_factors WHERE category_id = ?', (id,)
    # ).fetchone()
    # if has_factors:
    #     flash('无法删除包含因子数据的分类', 'danger')
    #     return redirect(url_for('factor_category.category_list'))
    
    db.execute('DELETE FROM factor_categories WHERE id = ?', (id,))
    db.commit()
    flash('分类删除成功', 'success')
    return redirect(url_for('factor_category.category_list'))